import 'package:dartkit/dartkit.dart';
import 'package:postgres/postgres.dart';

part 'model.dart';

class ThoughtTable extends TableForUser<Thought> {
  @override
  final String tableName = "thoughts";

  @override
  final Connection conn;

  @override
  Thought unzip(Json raw) => Thought.fromRaw(raw);

  ThoughtTable(this.conn);

  @override
  Future<void> createTable() async {
    await conn.execute("""
      CREATE TABLE IF NOT EXISTS $tableName (
        id SERIAL,
        user_id INT NOT NULL,
        timestamp TIMESTAMP WITH TIME ZONE NOT NULL,
        content TEXT NOT NULL,
        PRIMARY KEY (id, user_id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
      );
    """);

    await conn.execute("""
      CREATE INDEX IF NOT EXISTS idx_${tableName}_timestamp ON $tableName (timestamp);
    """);
  }

  @override
  Future<Thought> create(Json args) async {
    // 获取id和内容
    final int userId = args.getField<int>("user_id", "创建想法必须提供用户id(user_id)");
    final String content =
        args.getField<String>("content", "创建想法必须提供内容(content)");

    // 将数据插入数据库，并反悔结果
    final result = await conn.execute(Sql.named("""
      INSERT INTO $tableName (user_id, timestamp, content)
      VALUES (@user_id, NOW(), @content)
      RETURNING *;
    """), parameters: {
      'user_id': userId,
      'content': content,
    });
    if (result.isEmpty) throw BugDev("创建想法失败，未能插入记录");

    // 返回恢复的对象
    return Thought.fromRaw(result.first.toColumnMap());
  }

  @override
  Future<void> update(Thought item) async {
    final result = await conn.execute(Sql.named("""
    UPDATE $tableName
    SET content = @content, timestamp = @timestamp
    WHERE id = @id;
  """), parameters: {
      'id': item.id,
      'timestamp': item.timestamp,
      'content': item.content,
    });
    // 如果没有列表被修改
    if (result.affectedRows == 0) throw BugDev("更新失败：未找到匹配的想法");
  }
}

extension ThoughtTableExtraSql on ThoughtTable {
  Future<List<Thought>> findBetween(
    Period period, {
    required int userId,
  }) async {
    final results = await conn.execute(Sql.named("""
    SELECT *
    FROM $tableName
    WHERE user_id = @user_id AND timestamp BETWEEN @begin AND @end
    ORDER BY timestamp DESC;
  """), parameters: {
      'user_id': userId,
      'begin': period.begin,
      'end': period.end,
    });

    return results.map((row) => Thought.fromRaw(row.toColumnMap())).toList();
  }
}

final ThoughtTable thoughtTable = ThoughtTable(argConn.value);
